import base64
import sys
import pathlib
current_dir = pathlib.Path(__file__)
grandparent_dir = current_dir.parent.parent
sys.path.append(str(grandparent_dir.expanduser()))
import os
import re
from datetime import date, timedelta
from assist.MysqlTool import MysqlTool
import time
import pandas as pd
import requests
import numpy as np
import warnings
# from assist.TradingDay import isTradingDay


warnings.filterwarnings(action='ignore')
# ----------------------------------------------------------------------------------
# BCT 信息设置
# username = 'yuanyi'
# password = 'Qwer@1234'
username = 'script'
# password = '123456a.'
password = '50464626@Ebscn'
# ----------------------------------------------------------------------------------
# bct服务
# bct_url = "http://10.1.6.61"
# 测试环境
# bct_url = "http://10.84.185.144"
# 生产环境
bct_url = "http://10.80.54.168"
user_agent = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.99 Safari/537.36 Edg/97.0.1072.69 '
# ----------------------------------------------------------------------------------
# 设置搜索数量
today = date.today()
# 日期格式 2021-07-21，默认今天
search_date = str(today)
# search_date = '2022-06-01'
# search_date = str(datetime.date.today())
yesterday = today + timedelta(-1)
yesterday_str = str(today + timedelta(-1))
# -----------------------------------------------------------------------------------

mysql_processor = MysqlTool()
pd.set_option('display.float_format', lambda x: '%.2f'%x)

def login(username: str, password: str, host: str, port: int, method: str = None) -> dict:
    """
    登陆 - 获取token
    :param username: 登陆用户名
    :param password: 登陆密码
    :param host: 服务器地址
    :param port: 服务器端口
    :param method: 如果设为base64的话，会对用户名密码进行base64
    :return: 登陆头，包含了访问所需的token
    """
    url = '%s/auth-service/users/login' % url_builder(host, port)
    user = username
    pwd = password
    print("login with %s" % (user))
    if method == 'base64':
        user = base64.b64encode(username.encode(encoding='utf-8')).decode(
            encoding='utf-8')
        pwd = base64.b64encode(password.encode(encoding='utf-8')).decode(encoding='utf-8')
    body = {
        'username': user,
        'password': pwd
    }
    res = requests.post(url, json=body)
    try:
        json = res.json()
        print("login success")
        print("token:%s" % json['result']['token'])
    except Exception as e:
        print(e)
        print('\t=>' + res.text)
        print(res.headers)
        raise RuntimeError('error logging in: ')
    if 'error' in json:
        raise RuntimeError('error logging in: ' + json['error']['message'])
    token = json['result']['token']
    headers = {
        'Authorization': 'Bearer ' + token
    }
    return headers

def _get_token(auth_username: str, auth_password: str, server_url: str, base64_encode: bool = True) -> str:
    auth_url = "%s%s" % (server_url, '/api/auth-service/users/login')

    if base64_encode:
        usr_name = base64.b64encode(auth_username.encode())
        usr_pwd = base64.b64encode(auth_password.encode())
    else:
        usr_name = auth_username
        usr_pwd = auth_password

    auth_body = {
        'userName': usr_name.decode(),
        'password': usr_pwd.decode()
    }
    headers = {'User-Agent': user_agent}
    auth = requests.post(auth_url, json=auth_body, headers=headers)
    try:
        _token = auth.json()['token']
        return _token
    except Exception:
        error_message = auth.json()['errorMessage']
        raise ValueError(error_message)

def url_builder(host: str, port: int = 80) -> str:
    """
    按照host和port构建一个标准url
    :param host:
    :param port:
    :return:
    """
    protocol_prefix = re.match(r'^(http|https)://[^\n\r]+', host)
    if protocol_prefix:
        return '%s:%s' % (host, port)
    else:
        protocol = 'http'
        if port == 443:
            protocol = 'https'
        return '%s://%s:%s' % (protocol, host, port)

# 得到对应表格
def get_result_df(form_type, data, search_date):

    output_df = pd.DataFrame()
    # file_name = '空表格.xlsx'
    # 日终报告
    if form_type == '日终报告':
        result = data.get('result').get('page')

        if len(result) == 0:
            print('搜索日期为:' + search_date + ' 暂无日终报告\n')

        else:
            print('搜索日期为:' + search_date + ' 日终报告中包含' + str(len(result)) + '条交易记录\n')

            output_df = get_output_df(result)
            # file_name = '持仓明细' + search_date[5:7] + search_date[8:10] + '.xlsx'

    # 实时盈亏
    elif form_type == '实时盈亏':
        result = data.get('result')
        if len(result) == 0:
            print('搜索日期为:' + search_date + ' 暂无实时盈亏\n')
        else:
            output_df = get_output_df(result)

            # 报告生成时间
            time_created = str(output_df['createdAt'][0][:-7])
            print('实时风控报告生成时间为：' + time_created)
            print('实时风控报告包含条目数量为：' + str(len(result)) + '\n')
            time_name = time_created.replace(' ', '_').replace(':', '-')
            # file_name = '实时风控' + search_date[5:7] + search_date[8:10] + '.xlsx'

    # 主体业务监测一
    elif form_type == '主体业务监测一':
        result = data.get('result')

        if len(result) == 0:
            print('搜索日期为:' + search_date + ' 暂无主体业务监测报告一\n')
        else:
            print('搜索日期为:' + search_date + ' 主体业务监测报告一中包含' + str(len(result)) + '条交易记录\n')
            output_df = get_output_df(result)
            # file_name = '主体业务监测报告一_' + search_date[5:7] + search_date[8:10] + '.xlsx'

    # 主体业务监测二
    elif form_type == '主体业务监测二':
        result = data.get('result')

        if len(result) == 0:
            print('搜索日期为:' + search_date + ' 暂无主体业务监测报告二\n')
        else:
            print('搜索日期为:' + search_date + ' 主体业务监测报告二中包含' + str(len(result)) + '条交易记录\n')
            output_df = get_output_df(result)
            # file_name = '主体业务监测报告二_' + search_date[5:7] + search_date[8:10] + '.xlsx'

        # 主体业务监测二
    elif form_type == '交易现金流-已发生':
        result = data.get('page')

        if len(result) == 0:
            print('搜索日期为:' + search_date + form_type + ' 暂无\n')
        else:
            print('搜索日期为:' + search_date + form_type + ' 中包含' + str(
                len(result)) + '条交易记录\n')
            output_df = get_output_df(result)
            # file_name = '交易现金流-已发生_' + search_date[5:7] + search_date[8:10] + '.xlsx'

    elif form_type == '交易现金流-待发生':
        result = data.get('page')

        if len(result) == 0:
            print('搜索日期为:' + search_date + form_type + ' 暂无\n')
        else:
            print('搜索日期为:' + search_date + ' form_type中包含' + str(len(result)) + '条交易记录\n')
            output_df = get_output_df(result)
            # file_name = form_type + '_' + search_date[5:7] + search_date[8:10] + '.xlsx'

    elif form_type == '预付金现金流':
        result = data.get('page')

        if len(result) == 0:
            print('搜索日期为:' + search_date + form_type + ' 暂无\n')
        else:
            print('搜索日期为:' + search_date + form_type + ' 中包含' + str(
                len(result)) + '条交易记录\n')
            output_df = get_output_df(result)
            # file_name = form_type + '_' + search_date[5:7] + search_date[8:10] + '.xlsx'

    elif form_type == '同一主体业务监测信息汇总表(一)':
        result = data

        if len(result) == 0:
            print('搜索日期为:' + search_date + form_type + ' 暂无\n')
        else:
            print('搜索日期为:' + search_date + form_type + ' 中包含' + str(
                len(result)) + '条交易记录\n')
            output_df = get_output_df1(result)
            # file_name = form_type + '_' + search_date[5:7] + search_date[8:10] + '.xlsx'

    elif form_type == '同一主体业务监测信息汇总表(二)':
        result = data

        if len(result) == 0:
            print('搜索日期为:' + search_date + form_type + ' 暂无\n')
        else:
            print('搜索日期为:' + search_date + form_type + ' 中包含' + str(
                len(result)) + '条交易记录\n')
            output_df = get_output_df1(result)
            # file_name = form_type + '_' + search_date[5:7] + search_date[8:10] + '.xlsx'
    # 表格输入错误
    else:
        print('无' + '"' + form_type + '"' + '表格类型')

    return output_df

# input:表格类型、用户信息、token; output: 表格Dataframe, 文件名
def get_form(form_type, search_date, conf, flag=False):
    # 获得对应token

    # 参数设置
    token = _get_token(username, password, bct_url)
    headers = {"Authorization": "Bearer " + token}

    # 获得json 数据
    r = requests.post(url=bct_url + conf.get('url'),
                      json=conf.get('param'), headers=headers)
    data = r.json()  # 接口响应参数json化
    output_df = get_result_df(form_type, data, search_date)

    return output_df

# Json 转换成 pandas Dataframe
def get_output_df(result):
    output_df = pd.DataFrame(result[0], index=[0])
    for i in range(len(result) - 1):
        output_df_temp = pd.DataFrame(result[i + 1], index=[0])
        output_df = pd.concat([output_df, output_df_temp])
    output_df = output_df.reset_index(drop=True)

    return output_df

def get_output_df1(result):
    output_df = pd.DataFrame(result[0])
    for i in range(len(result) - 1):
        output_df_temp = pd.DataFrame(result[i + 1])
        output_df = pd.concat([output_df, output_df_temp])
    output_df = output_df.reset_index(drop=True)

    return output_df

def direct_import(url_1='', json={}):
    url = bct_url + url_1
    token = _get_token(username, password, bct_url)
    headers = {"Authorization": "Bearer " + token}
    r = requests.post(url, headers=headers, json=json)
    df = pd.read_excel(r.content)

    return df

def cash():
    # 1.交易现金流-已发生
    form_type_12 = "交易现金流-已发生"
    transaction_cashflow_incurred_df = get_form(form_type_12,
                                                search_date,
                                                {
                                                    "name": form_type_12,
                                                    'param': {"cashFlowStatus": "NORMAL",
                                                              "page": 0, "pageSize": 100000,
                                                              "category": "REALIZED"},
                                                    'url': '/bct/capital-service/trade-cash-flows/list'
                                                },
                                                flag=True
                                                )

    # 2.交易现金流-待发生
    form_type_12 = "交易现金流-待发生"
    transaction_cashflow_tobe_incurred = get_form(form_type_12,
                                                  search_date,
                                                  {
                                                      "name": form_type_12,
                                                      'param': {"cashFlowStatus": "NORMAL",
                                                                "page": 0, "pageSize": 100000,
                                                                "category": "UNREALIZED"},
                                                      'url': '/bct/capital-service/trade-cash-flows/list'
                                                  },
                                                  flag=True
                                                  )

    # 3.预付金现金流
    form_type_13 = "预付金现金流"
    cashflow_advances = get_form(form_type_13,
                                 search_date,
                                 {
                                     "name": form_type_13,
                                     'param': {"cashFlowStatus": "NORMAL",
                                               "page": 0, "pageSize": 100000},
                                     'url': '/bct/capital-service/margin-cash-flows/list'
                                 },
                                 flag=True
                                 )

    return transaction_cashflow_incurred_df, transaction_cashflow_tobe_incurred, cashflow_advances

# 主体
def subject():
    # 同一主体业务监测信息汇总表(一)

    # todo 根据生产环境修改相应报告名称及统计维度人员类型
    reportName = '日终报告-持仓明细_默认定价环境'
    personType = '控股股东'

    # 同一主体业务监测信息汇总表(一)
    form_type_13 = "同一主体业务监测信息汇总表(一)"
    output_df_13 = get_form(form_type_13,
                            search_date,
                            {
                                "name": form_type_13,
                                'param': {"reportDate": search_date,
                                          "personType": personType,
                                          "reportName": reportName},
                                'url': '/bct/report-service/same-subject/one-get'
                            },
                            flag=True
                            )

    # 同一主体业务监测信息汇总表(二)
    form_type_14 = "同一主体业务监测信息汇总表(二)"
    output_df_14 = get_form(form_type_14,
                            search_date,
                            {
                                "name": form_type_14,
                                'param': {"reportDate": search_date,
                                          "personType": personType,
                                          "reportName": reportName},
                                'url': '/bct/report-service/same-subject/two-get'
                            }
                            ,
                            flag=True
                            )

    return output_df_13, output_df_14

"""
情景分析
"""
# 分析维度-标的物价格+波动率

analysis_dimension1 = {
    "config": {
        "volMin": 0,  # 波动率偏移范围(%) : -
        "volMax": 0,
        "spotMin": 0.8,  # 价格范围(%) : x
        "spotMax": 1.2,
        # "pricingEnvironment": "Default", #  定价环境
        "pricingEnvironment": "积分法环境",
        "spotNum": 5,  # 价格情景个数
        "volNum": 1,  # 波动率情景个数
        "valuationDate": search_date  # 估值日
    },
    "scope": {
        "bookNames": [  # 交易簿
            # "李四交易簿"
            "金创-权益类-指数期权001"
        ]
    },
    # 标的物价格+波动率
    "analyseDimension": "spotVol"
}
analysis_dimension4 = {
    "config": {
        "volMin": -0.1,  # 波动率偏移范围(%) : -
        "volMax": 0.1,
        "spotMin": 1,  # 价格范围(%) : x
        "spotMax": 1,
        # "pricingEnvironment": "Default", #  定价环境
        "pricingEnvironment": "积分法环境",
        "spotNum": 1,  # 价格情景个数
        "volNum": 5,  # 波动率情景个数
        "valuationDate": search_date  # 估值日
    },
    "scope": {
        "bookNames": [  # 交易簿
            # "李四交易簿"
            "金创-权益类-指数期权001"
        ]
    },
    # 标的物波动率+价格
    "analyseDimension": "volSpot"
}
# 时间+标的物价格 注释见上
analysis_dimension2 = {
    "config": {
        "volMin": 0,
        "volMax": 0,
        "spotMin": 0.8,
        "spotMax": 1.2,
        "pricingEnvironment": "积分法环境",
        "spotNum": 5,
        "volNum": 1,
        "valuationDateBegin": yesterday_str,
        "valuationDateEnd": search_date
    },
    "scope": {
        "bookNames": [
            "金创-权益类-指数期权001"
        ]
    },
    "analyseDimension": "timeSpot"
}
# 时间+波动率
analysis_dimension3 = {
    "config": {
        "volMin": -0.06,
        "spotMin": 1,
        "spotMax": 1,
        "volMax": 0.06,
        "pricingEnvironment": "积分法环境",
        "spotNum": 1,
        "volNum": 5,
        "valuationDateBegin": yesterday_str,  # 估值起始日
        "valuationDateEnd": search_date
    },
    "scope": {
        "bookNames": [
            "金创-权益类-指数期权001"
        ]
    },
    "analyseDimension": "timeVol"
}

def startPrcScenario(param):
    title_pre = '情景分析'
    token = _get_token(username, password, bct_url)
    headers = {"Authorization": "Bearer " + token}
    scenario_dict = {
        "timeSpot": "时间+标的物价格",
        "spotVol": "标的物价格+波动率",
        "timeVol": "时间+波动率",
        "volSpot": "波动率+价格"
    }
    # 获得json 数据
    r = requests.post(url=bct_url + '/bct/margin-service/startPrcScenario', json=param, headers=headers)
    data = r.json()
    if data.get('msg') != 'ok':
        print("正在runing 调用情景分析失败，请查看服务是否正常。。。。。")
    else:
        while True:
            scenario_json = {
                "results": [],
                "completed": False,
                "crashed": False,
                "total": 3,
                "processed": 0,
                "startAt": "2022-06-02T16:17:35.788",
                "finishAt": "1970-01-08T15:18:43.2",
                "request": param
            }
            result = requests.get(url=bct_url + '/bct/margin-service/getPrcScenarioResult', json=scenario_json,
                                  headers=headers)
            res = result.json()
            type = scenario_dict.get(param.get("analyseDimension"))
            if res.get('completed'):
                print("情景分析[%s]结束" % type)
                # print(res)
                response = res.get("results")
                if type == '时间+标的物价格':
                    spot_100_pv = {}
                    for res in response:
                        if res.get("spotId") == 'SPOT_100.0000%':
                            spot_100_pv[res.get("instrumentId")+ res.get('valuationDateTime')] = res.get("pv")
                    for res in response:
                        res["pnl"] = res.get("pv") - spot_100_pv.get(res.get("instrumentId")+ res.get('valuationDateTime'))
                elif type == '标的物价格+波动率':
                    pass
                elif type == '时间+波动率':
                    pass
                df = get_output_df(response)
                # df.to_excel(search_date + "/" + "情景分析" + type + '_' + search_date[5:7] + search_date[8:10] + '.xlsx',
                #             index=False)
                break
            else:
                time.sleep(30)
                print("%s--情景分析未完成,继续执行" % type)

    return df

def start_all_prcScenario():
    """
    {
        "timeSpot" : "时间+标的物价格", # analysis_dimension2
        "spotVol" : "标的物价格+波动率", # analysis_dimension1
        "timeVol" : "时间+波动率"     # analysis_dimension3
    }
    todo analysis_dimension对参数修改 对应的交易簿及情景分析各个参数等
        analysis_dimension1,2,3对应上述三种分析维度
    """
    scenario_underlyingprice_volatility_df = startPrcScenario(analysis_dimension1)  # 标的物价格+波动率
    scenario_underlyingvolatility_price_df = startPrcScenario(analysis_dimension4)
    # startPrcScenario(analysis_dimension2)   # 时间+标的物价格
    # startPrcScenario(analysis_dimension3)   # 时间+波动率

    return scenario_underlyingprice_volatility_df, scenario_underlyingvolatility_price_df

def subtotal_price_vol_process(df, underlying_asset):

    df1 = df[df.instrumentId == underlying_asset]
    df2 = df1[['instrumentId', 'spotId', 'pv', 'deltaCash', 'gammaCash', 'vega', 'theta']]
    df2.iloc[:, 2:] = df2.iloc[:, 2:] / 100000
    df2.iloc[:, 2:] = df2.iloc[:, 2:].applymap(lambda x: '%.2f' % x)
    df2.iloc[:, 1] = df2.iloc[:, 1].apply(lambda x: format(float(x[5:10]) / 100), '.2%')
    df3 = df2.sort_values(by='spotId', ascending=False)
    df3.iloc[:, 1] = df3.iloc[:, 1].apply(lambda x: '{:.2%}'.format(float(x)))
    df3.set_index('spotId', inplace=True)
    df4 = df3.reset_index()

    return df4

def subtotal_vol_price_process(df, underlying_asset):

    df1 = df[df.instrumentId == underlying_asset]
    df2 = df1[['instrumentId', 'volId', 'pv', 'deltaCash', 'gammaCash', 'vega', 'theta']]
    df2.iloc[:, 2:] = df2.iloc[:, 2:] / 100000
    df2.iloc[:, 2:] = df2.iloc[:, 2:].applymap(lambda x: '%.2f' % x)
    df2.iloc[:, 1] = df2.iloc[:, 1].apply(lambda x: format(float(x[4:-1]) / 100), '.2%')
    df3 = df2.sort_values(by='volId', ascending=False)
    df3.iloc[:, 1] = df3.iloc[:, 1].apply(lambda x: '{:.2%}'.format(float(x)))
    df3.set_index('volId', inplace=True)
    df4 = df3.reset_index()

    return df4

# @isTradingDay
def execute_all():

    scenario_underlyingprice_volatility_df, scenario_underlyingvolatility_price_df = start_all_prcScenario()
    # mysql_processor.df_insert_to_sql(scenario_underlyingprice_volatility_df, 'scenario_underlyingprice_volatility')
    mysql_processor.df_insert_to_sql(scenario_underlyingvolatility_price_df, 'scenario_underlyingvolatility_price')
    price_vol_data = subtotal_price_vol_process(scenario_underlyingprice_volatility_df, '000905.SH')
    # price_vol_data.to_excel('price_vol.xlsx', index=False)
    # mysql_processor.df_insert_to_sql(price_vol_data, 'subtotal_price_vol')

    vol_price_data = subtotal_vol_price_process(scenario_underlyingvolatility_price_df, '000905.SH')
    # price_vol_data.to_excel('vol_price.xlsx', index=False)
    mysql_processor.df_insert_to_sql(vol_price_data, 'subtotal_vol')
    return 

    transaction_cashflow_incurred_df, transaction_cashflow_tobe_incurred_df, cashflow_advances_df = cash()
    mysql_processor.df_insert_to_sql(transaction_cashflow_incurred_df, 'transaction_cashflow_incurred')
    mysql_processor.df_insert_to_sql(transaction_cashflow_tobe_incurred_df, 'transaction_cashflow_tobe_incurred')
    mysql_processor.df_insert_to_sql(cashflow_advances_df, 'cashflow_advances')

    form_type_2 = "实时盈亏"
    pl_dict_report = {
        "name": '实时盈亏',
        'param': {"method": "rptIntradayMegaReportGet",
                  "params": {'lived': "true"}
                  },
        'url': '/api/report-service/api/rpc'
    }
    realtime_riskcontrol_df = get_form(form_type_2, search_date, pl_dict_report)
    mysql_processor.df_insert_to_sql(realtime_riskcontrol_df, 'realtime_riskcontrol')

    # 场外衍生品报表
    otc_derivatives_report_df = direct_import('/bct/plugins/bct2DailyReportController/bct2DailyReportExcel',
                                              json={"valuationDate": search_date,
                                                    "page": 0, "pageSize": 100000,
                                                    "total": 0})
    col_final = ['positionID', 'bookName', 'counterpartyName', 'direction', 'productType',
                 'underlyingCode', 'underlyingType', 'period', 'startDate', 'endDate',
                 'settlementDate', 'status', 'notionalPrincipal', 'contractOptionFee', 'guaranteedReturn',
                 'optionPremium', 'marketValue', 'closeoutAmount', 'PNL', 'valuationPNL',
                 'counterpartyType', 'businessType', 'contractID', 'confirmationNo']
    otc_derivatives_report_df.columns = col_final
    mysql_processor.df_insert_to_sql(otc_derivatives_report_df, 'otc_derivatives_report')


    return None

#用于测试环境下的入库，所入的库都加上

# @isTradingDay
def execute_all_test():

    scenario_underlyingprice_volatility_df, scenario_underlyingvolatility_price_df = start_all_prcScenario()
    mysql_processor.df_insert_to_sql(scenario_underlyingprice_volatility_df, 'scenario_underlyingprice_volatility_test')
    mysql_processor.df_insert_to_sql(scenario_underlyingvolatility_price_df, 'scenario_underlyingvolatility_price_test')

    price_vol_data = subtotal_price_vol_process(scenario_underlyingprice_volatility_df, '000905.SH')
    # price_vol_data.to_excel('price_vol.xlsx', index=False)
    mysql_processor.df_insert_to_sql(price_vol_data, 'subtotal_price_vol_test')

    vol_price_data = subtotal_vol_price_process(scenario_underlyingvolatility_price_df, '000905.SH')
    # price_vol_data.to_excel('vol_price.xlsx', index=False)
    mysql_processor.df_insert_to_sql(vol_price_data, 'subtotal_vol_price_test')

    transaction_cashflow_incurred_df, transaction_cashflow_tobe_incurred_df, cashflow_advances_df = cash()
    mysql_processor.df_insert_to_sql(transaction_cashflow_incurred_df, 'transaction_cashflow_incurred_test')
    mysql_processor.df_insert_to_sql(transaction_cashflow_tobe_incurred_df, 'transaction_cashflow_tobe_incurred_test')
    mysql_processor.df_insert_to_sql(cashflow_advances_df, 'cashflow_advances_test')

    form_type_2 = "实时盈亏"
    pl_dict_report = {
        "name": '实时盈亏',
        'param': {"method": "rptIntradayMegaReportGet",
                  "params": {'lived': "true"}
                  },
        'url': '/api/report-service/api/rpc'
    }
    realtime_riskcontrol_df = get_form(form_type_2, search_date, pl_dict_report)
    mysql_processor.df_insert_to_sql(realtime_riskcontrol_df, 'realtime_riskcontrol_test')

    # 场外衍生品报表
    otc_derivatives_report_df = direct_import('/bct/plugins/bct2DailyReportController/bct2DailyReportExcel',
                                              json={"valuationDate": search_date,
                                                    "page": 0, "pageSize": 100000,
                                                    "total": 0})
    # print(otc_derivatives_report_df)
    col_final = ['positionID', 'bookName', 'counterpartyName', 'direction', 'productType',
                 'underlyingCode', 'underlyingType', 'period', 'startDate', 'endDate',
                 'settlementDate', 'status', 'notionalPrincipal', 'contractOptionFee', 'guaranteedReturn',
                 'optionPremium', 'marketValue', 'closeoutAmount', 'PNL', 'valuationPNL',
                 'counterpartyType', 'businessType', 'contractID', 'confirmationNo']
    otc_derivatives_report_df.columns = col_final
    mysql_processor.df_insert_to_sql(otc_derivatives_report_df, 'otc_derivatives_report_test')
    return None


if __name__ == '__main__':
    if len(sys.argv) >= 2:
        if sys.argv[1] == "test":
            execute_all_test()
            # print(table_name_list_test)
    else:
        execute_all()
        print('load_bct_data0112.py 文件已落库完毕')
